package com.x.erp.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.x.erp.model.Shop;
import com.x.framework.dao.BaseDao;

@Repository
public class ShopDao extends BaseDao {
	// 查询列表sql
	private final static String selectShopListSql = "SELECT es.*, em.MARKET_NAME,em.MARKET_ADDRESS,em.MARKET_LAT,em.MARKET_LNG,em.MARKET_MOBILE,"
			+ "(SELECT COUNT(ec.CAR_ID) from ERP_CAR ec WHERE ec.SHOP_ID = es.SHOP_ID) as CAR_NUM "
			+ "FROM ERP_SHOP es, ERP_MARKET em" + " WHERE es.MARKET_ID = em.MARKET_ID ";

	/**
	 * @param shop
	 * 封装了shop的检索条件
	 * @return
	 * @throws Exception
	 */
	public List<Shop> selectShopList(Shop shop) throws Exception {
		StringBuilder sql = new StringBuilder(selectShopListSql);
		List<Object> params = new ArrayList<Object>();
		selectSearch(sql, params, shop);
		sql.append("ORDER BY es.SHOP_CREATE_TIME DESC ");
		return this.queryForList(sql.toString(), Shop.class, params, shop);
	}

	private final static String selectShopNumSql = "SELECT count(es.shop_id) "
			+ "FROM ERP_SHOP es, ERP_MARKET em " + "WHERE es.MARKET_ID = em.MARKET_ID ";

	public int selectShopNum(Shop shop) {
		StringBuilder sql = new StringBuilder(selectShopNumSql);
		List<Object> params = new ArrayList<Object>();
		selectSearch(sql, params, shop);
		return this.queryForInt(sql.toString(), params);
	}

	private final static String selectPledgeShopNumSql = "SELECT COUNT(DISTINCT ec.SHOP_ID) FROM ERP_CAR ec WHERE ec.CAR_PLEDGE_STATE = 310 ";
	public int selectPledgeShopNum(Shop shop) {
		StringBuilder sql = new StringBuilder(selectPledgeShopNumSql);
		List<Object> params = new ArrayList<Object>();
		if(StringUtils.isNotEmpty(shop.getMarketId())){
			sql.append("AND ec.MARKET_ID = ? ");
			params.add(shop.getMarketId());
		}
		if(StringUtils.isNotEmpty(shop.getShopId())){
			sql.append("AND ec.SHOP_ID = ? ");
			params.add(shop.getShopId());
		}
		return this.queryForInt(sql.toString(), params);
	}

	private void selectSearch(StringBuilder sql, List<Object> params, Shop shop) {
		if (this.isNotNull(shop.getMarketId())) {
			sql.append("AND es.MARKET_ID = ? ");
			params.add(shop.getMarketId());
		}
		if (this.isNotNull(shop.getShopId())) {
			sql.append("AND es.SHOP_ID = ? ");
			params.add(shop.getShopId());
		}
		if (this.isNotNull(shop.getShopMappingId())) {
			sql.append("AND es.SHOP_MAPPING_ID = ? ");
			params.add(shop.getShopMappingId());
		}
		if (this.isNotNull(shop.getShopName())) {
			sql.append("AND es.SHOP_NAME LIKE ? ");
			params.add(shop.getShopName() + "%");
		}
		if (this.isNotNull(shop.getShopMobile())) {
			sql.append("AND es.SHOP_MOBILE LIKE ? ");
			params.add(shop.getShopMobile() + "%");
		}
		if (this.isNotNull(shop.getShopState())) {
			sql.append("AND es.SHOP_STATE = ? ");
			params.add(shop.getShopState());
		}
		if(shop.getSearchStartTime() != null){
			sql.append("AND es.SHOP_CREATE_TIME >= ? ");
			params.add(shop.getSearchStartTime());
		}
		if(shop.getSearchEndTime() != null ){
			sql.append("AND es.SHOP_CREATE_TIME <= ? ");
			params.add(shop.getSearchEndTime());
		}
	}

}
